package com.test.study.jdbc;

/**
 * JDBC 的批处理
 * @author huyong
 *
 */

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.logging.Logger;

import org.junit.Test;

public class JdbcBatchDemo {
	private static final Logger log = Logger.getLogger(JdbcBatchDemo.class.getName());

	@Test
	public void testBatch() {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			connection = JdbcUtils.getConnection();
			statement = connection.createStatement();
			String sql0 = "drop database test1";
			String sql1 = "create database test1";
			String sql2 = "use test1";
			String sql3 = "create table user(id int primary key auto_increment,name varchar(20))";
			String sql4 = "insert into user values(null,'aaa')";
			String sql5 = "insert into user values(null,'bbb')";
			String sql6 = "insert into user values(null,'ccc')";
			String sql7 = "update user set name='www' where id=2";
			String sql8 = "delete from user where id=1";
			statement.addBatch(sql0);
			statement.addBatch(sql1);
			statement.addBatch(sql2);
			statement.addBatch(sql3);
			statement.addBatch(sql4);
			statement.addBatch(sql5);
			statement.addBatch(sql6);
			statement.addBatch(sql7);
			statement.addBatch(sql8);

			int[] batch = statement.executeBatch();

			log.info("batch sql count==> " + batch);

		} catch (Exception e) {
			log.info(e.getMessage());
		} finally {
			JdbcUtils.close(connection, statement, resultSet);
		}
	}
/**
 * 默认清空下 mysql 数据 没有开启批处理 需要在url 后面添加参数 
 */
	@Test
	public void testSaveBatch() {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		long startTime=System.currentTimeMillis();
		try {
			connection = JdbcUtils.getConnection();
			String sql = "insert into user values(null,?)";
			statement = connection.prepareStatement(sql);
			for (int i = 1; i < 10000; i++) {
				statement.setString(1, "name"+i);
				statement.addBatch();
				//注意 这里很容易导致内存溢出
				// 执行批处理
				if(i%1000==0) {
					int[] executeBatch = statement.executeBatch();
					log.info("executeBatch count ==> "+executeBatch.length);
					//清空批处理中的数据，防止内存溢出
					statement.clearBatch();
				}
			}
			int[] batch = statement.executeBatch();

			log.info("batch sql count==> " + batch);

		} catch (Exception e) {
			log.info(e.getMessage());
		} finally {
			JdbcUtils.close(connection, statement, resultSet);
			log.info("耗时:"+(System.currentTimeMillis()-startTime));
		}
	}

}
